using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using CSI.Directory.Core.Model;
using CSI.Directory.Core.Repositories;
using CSI.Directory.Core.Services;
using SubSonic.DataProviders;
using SubSonic.Repository;
using SubSonic.Schema;

namespace CSI.Directory.Repo
{
    public class DirectoryRepository : RepositoryBase, IDirectoryRepository
    {
        private IList<Category> categories;
        private IList<County> counties;
        private IList<Article> articles;
        private ICacheManager CacheManager;
        private IList<Category> Categories
        {
            get
            {

                if (categories == null)
                    Cache();
                return categories;
            }
            set { categories = value; }
        }
        private IList<County> Counties
        {
            get
            {

                if (counties == null)
                    Cache();
                return counties;
            }
            set { counties = value; }
        }
        public DirectoryRepository(ICacheManager c)
        {
            CacheManager = c;
            Categories = new List<Category>();
            if (CacheManager.Categories == null)
                Cache();
        }

        private IList<Article> Articles
        {
            get
            {

                if (articles == null)
                    Cache();
                return articles;
            }
            set { articles = value; }
        }
        public DirectoryRepository() { }
        private void Cache()
        {

            CacheManager.Cache(repo.All<Category>().ToList());
            CacheManager.Cache(repo.All<County>().ToList());
            categories = CacheManager.Categories;
            counties = CacheManager.Counties;
        }

        public IList<Category> GetAllCategories()
        {
            var sproc = new StoredProcedure("GetCategories", ProviderFactory.GetProvider("Eagle"));
            return sproc.ExecuteTypedList<Category>();
        }

        public IList<County> GetAllCounties()
        {
            return (Counties == null || Counties.Count == 0) ? repo.All<County>().ToList() : Counties;
        }
        

        public IList<Listing> GetListingsByCategory(int id) 
        {
            //var list = repo.Find<ListingCategory>(t => t.Category == id);
            //var listings = new List<Listing>();
            //foreach (var l in list)
            //{
            //    var listing = repo.Find<Listing>(t => t.Id == l.Listing).Where(t => t.Approved).SingleOrDefault();
            //    if (listing != null) listings.Add(listing);
            //}
            
            //return listings;

            var l =  (from listings in repo.All<Listing>()
                    join lcat in repo.All<ListingCategory>()
                        on listings.Id equals lcat.Listing
                    where lcat.Category == id
                    select listings).ToList();
            foreach (var listing in l)
            {
                listing.Categories = (from cat in Categories 
                                      join lcat in repo.All<ListingCategory>() on cat.Id equals lcat.Category
                                      where lcat.Listing == listing.Id
                                      select cat).ToList();
            }
            //foreach (var listing in l)
            //{
            //    listing.Counties = (from cat in Counties
            //                          join lcat in repo.All<ListingCounty>() on cat.Id equals lcat.County
            //                          select cat).ToList();
            //}
            return l.Where(t => t.Active == true).ToList(); ;
        }

        public Category GetParentCategoryByName(string name)
        {
            return (Categories == null || Categories.Count == 0) ? repo.Single<Category>(t => t.Name == name && t.ParentId == 0) : Categories.Single(t => t.Name == name && t.ParentId == 0);
        }

        public Category GetCategoryByName(string name)
        {
            return (Categories == null || Categories.Count == 0) ? repo.Single<Category>(t => t.Name == name) : Categories.Single(t => t.Name == name);
        }

        public IList<Category> GetSubCategoriesForCategory(int id)
        {
            return (Categories == null || Categories.Count == 0) ? repo.Find<Category>(t => t.ParentId == id) : Categories.Where(t => t.ParentId == id).ToList();
        }
        public TransactionResult AddListing(Listing list, IList<ListingCategory> categories)
        {
            var id = repo.Add(list);
            if (id == null) return new TransactionResult() { Exception = "Null id after save", Successful = false };
            foreach (var category in categories)
            {
                category.Listing = Int32.Parse(id.ToString());
            }

            repo.AddMany(categories);
            return new TransactionResult() { Successful = true };
        }

        public TransactionResult ManageListing(IList<Listing> listings)
        {

            var result = repo.UpdateMany(listings);
            return new TransactionResult() { Successful = true, ReturnVal = result };
        }

        public Listing GetListingById(int id)
        {
            var listing =  repo.Single<Listing>(t => t.Id == id);
           
            listing.Categories = (from cat in Categories
                                      join lcat in repo.All<ListingCategory>() on cat.Id equals lcat.Category
                                      where lcat.Listing == id
                                      select cat).ToList();
           
            return listing;
        }

        public IList<ListingCategory> GetFeaturedListingCategories()
        {
            return repo.Find<ListingCategory>(t => t.Feature == true).ToList();

        }

        public TransactionResult RemoveFeature(ListingCategory listing)
        {
            var t = new TransactionResult { Successful = false, ReturnVal = repo.Update(listing) };
            if (t.ReturnVal > 0)
                t.Successful = true;

            return t;
        }

        

        public ListingCategory GetListingCategoryBy(int id)
        {
            return repo.Single<ListingCategory>(t => t.Id == id);
        }

        public IList<ListingCategory> GetListingCategoryByListingId(int id)
        {
            return repo.Find<ListingCategory>(t => t.Listing == id);
        }

        public IList<ListingCounty> GetListingCountiesByListingId(int id)
        {
            return repo.Find<ListingCounty> (t => t.Listing == id);
        }

        public Listing GetListingByUserId(int id)
        {
            return repo.Single<Listing>(t => t.UserId == id);
        }

        public IList<Listing> GetFeaturedListings()
        {
            return repo.Find<Listing>(t => t.Featured == true);
        }

        public County GetCountyByName(string name)
        {
            return repo.Single<County>(t => t.Name == name);
        }

        //public IList<Listing> GetListingsForManager()
        //{
        //    var listings = repo.Find<Listing>(t => t.Approved == false).Where(t => t.Denied == false).ToList();
        //    if (listings.Count == 0)
        //    {
        //        return listings;
        //    }
        //    foreach (var listing in listings)
        //    {
        //        listing.Categories = repo.Find<ListingCategory>(t => t.Listing == listing.Id);
        //        foreach (var c in listing.Categories)
        //        {
        //            c.Name = repo.Single<Category>(t => t.Id == c.Category).Name;
        //        }
        //    }
        //    return listings;
        //}

        public IList<ListingCategory> GetCategoriesForListing(Listing l)
        {

            return repo.Find<ListingCategory>(t => t.Listing == l.Id);
        }

        public Category GetCategoryById(int id)
        {
            return repo.Single<Category>(t => t.Id == id);
        }

        public TransactionResult UpdateCategory(Category c)
        {
            var t = new TransactionResult();
            t.ReturnVal = repo.Update<Category>(c);
            t.Successful = true;
            Cache();
            return t;
        }

        public TransactionResult AddCategory(Category category)
        {
            var t = new TransactionResult();
            var r = repo.Add(category);
            if (r == null) return new TransactionResult() { Exception = "Null id after save", Successful = false };
            t.Successful = true;
            Cache();
            return t;
        }


        public IList<Listing> SearchDirectory(string query, int category, int county)
        {
            var sproc = new StoredProcedure("DirectorySearch", ProviderFactory.GetProvider("Eagle"));
            sproc.Command.AddParameter("query", query, DbType.String);
            sproc.Command.AddParameter("category", category, DbType.Int32);
            sproc.Command.AddParameter("county", county, DbType.Int32);
            var r =  sproc.ExecuteTypedList<Listing>().Where(t=>t.Active==true).ToList();
            return r;
            if (category == 0 && county == 0)
                return repo.Find<Listing>(t => t.Description.ToUpper().Contains(query.ToUpper()) == true);
            var listings = new List<Listing>();
            if(category != 0)
            {
                var l = (from listing in repo.All<Listing>()
                                join lcat in repo.All<ListingCategory>() on listing.Id equals lcat.Listing
                                where lcat.Category.Equals(category) &&
                                listing.Description.ToUpper().Contains(query.ToUpper())
                                select listing).ToList();
                listings.AddRange(l.ToList());
            }
            if (county != 0)
            {
                var l = (from listing in repo.All<Listing>()
                         join lcounty in repo.All<ListingCounty>() on listing.Id equals lcounty.Listing
                         where lcounty.County.Equals(category)
                         && listing.Description.ToUpper().Contains(query.ToUpper())
                         select listing).ToList();
                listings.AddRange(l.ToList());
            }
            return listings;
            //var list = repo.All<Listing>().Where(t => t.Approved);
            //var allCount = list.ToList().Count;
            //var found = false;
            //if (!string.IsNullOrEmpty(name))
            //{

            //    var c = Categories.Where(t => t.Name.ToUpper().StartsWith(name.ToUpper())).ToList();
            //    if (c.Count > 0)
            //    {

            //        var lc = repo.All<ListingCategory>();
            //        foreach (var category in c)
            //        {
            //            var l = from listing in list
            //                    join lcat in lc on listing.Id equals lcat.Listing
            //                    where lcat.Category.Equals(category.Id)
            //                    select listing;
            //            list.ToList().AddRange(l.ToList());
            //            found = true;

            //        }

            //    }
            //    var resultsList = (from listing in list where listing.Name.ToUpper().StartsWith(name.ToUpper()) select listing);
            //    if (resultsList.ToList().Count > 0)
            //    {
            //        if (list.ToList().Count == allCount) //nothing has changed
            //        {
            //            list = resultsList;
            //        }
            //        else
            //        {
            //            list.ToList().AddRange(resultsList);
            //        }
            //        found = true;
            //    }

            //}

            //if (!string.IsNullOrEmpty(city))
            //{
            //    var resultsList = from listing in list where listing.City.ToUpper().StartsWith(city.ToUpper()) select listing;
            //    if (resultsList.ToList().Count > 0)
            //    {
            //        if (list.ToList().Count == allCount) //nothing has changed
            //        { list = resultsList; }
            //        else
            //        { list.ToList().AddRange(resultsList); }
            //        found = true;
            //    }

            //}
            //if (!string.IsNullOrEmpty(state))
            //{
            //    var resultsList = from listing in list where listing.State.ToUpper().StartsWith(state.ToUpper()) select listing;
            //    if (resultsList.ToList().Count > 0)
            //    {
            //        if (list.ToList().Count == allCount) //nothing has changed
            //        { list = resultsList; }
            //        else
            //        { list.ToList().AddRange(resultsList); }
            //        found = true;
            //    }
            //}


            //return (list.Count() == allCount && found == false) ? new List<Listing>() : list.ToList();


        }

        public TransactionResult DeleteListingCategory(int id)
        {
            var t = new TransactionResult { Successful = false, ReturnVal = repo.Delete<ListingCategory>(id) };
            if (t.ReturnVal > 0)
                t.Successful = true;

            return t;
        }
        public IList<ListingCategory> GetListingCategoryBy(Category c)
        {

            return repo.Find<ListingCategory>(t => t.Category == c.Id).ToList();
        }
        public TransactionResult DeleteCategory(int id)
        {
            var t = new TransactionResult { Successful = false, ReturnVal = repo.Delete<Category>(id) };
            if (t.ReturnVal > 0)
                t.Successful = true;
            Cache();
            return t;
        }

        public TransactionResult FeatureListing(ListingCategory l)
        {
            var tran = new TransactionResult() { Successful = false };
            var listingCategory =
                repo.Single<ListingCategory>(
                    t => t.Category == l.Category && t.Listing == l.Listing);
            if (listingCategory == null)
            {

                repo.Add(l);
                tran.ReturnVal = l.Id;
                if (tran.ReturnVal > 0)
                    tran.Successful = true;
                return tran;
            }
            if (listingCategory.Feature)
            {
                tran.Successful = true;
                return tran;
            }
            listingCategory.Feature = true;
            tran.ReturnVal = repo.Update(listingCategory);
            if (tran.ReturnVal > 0)
                tran.Successful = true;
            return tran;

        }

        /* Article Coding Start Here*/
        /// <summary>
        /// To get the articles based on the menu ID
        /// </summary>
        /// <param name="menuId"></param>
        /// <returns></returns>
        //public IList<Article> GetArticlesByMenuId(int menuId)
        //{
        //    return (Articles == null || Articles.Count == 0) ? repo.All<Article>().Where(menu => menu.MenuId == menuId).ToList<Article>() :
        //        Articles.Where(menu => menu.MenuId == menuId).ToList<Article>();
        //}

        /// <summary>
        /// To get the articles 
        /// </summary>
        /// <param name="menuId"></param>
        /// <returns></returns>
        public IList<Article> GetArticlesByMenuId(int id)
        {
            return (Articles == null || Articles.Count == 0)
                       ? repo.Find<Article>(t => t.MenuId == id)
                       :
                           Articles.Where(t => t.MenuId == id).ToList();
        }
        /// <summary>
        /// Get Article by Article id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Article GetArticleById(int id)
        {
            return repo.Single<Article>(t => t.Id == id);
        }

        /// <summary>
        /// Adding the Article
        /// </summary>
        /// <param name="article"></param>
        /// <returns></returns>
        public TransactionResult AddArticle(Article article)
        {
            var t = new TransactionResult();
            var r = repo.Add(article);
            if (r == null) return new TransactionResult() { Exception = "Null id after save", Successful = false };
            t.Successful = true;
            Cache();
            return t;
        }
        /// <summary>
        /// Updating the existing Article
        /// </summary>
        /// <param name="article"></param>
        /// <returns></returns>
        public TransactionResult UpdateArticle(Article article)
        {
            var t = new TransactionResult();
            t.ReturnVal = repo.Update<Article>(article);
            t.Successful = true;
            Cache();
            return t;
        }
        /// <summary>
        /// Delete the articl
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public TransactionResult DeleteArticle(int id)
        {
            var t = new TransactionResult { Successful = false, ReturnVal = repo.Delete<Article>(id) };
            if (t.ReturnVal > 0)
                t.Successful = true;
            return t;
        }

        /* Article Coding End Here */


        /* Menu Coding Start Here*/
        /// <summary>
        /// To get the Menu based on the ID
        /// </summary>
        /// <param name="menuId"></param>
        /// <returns></returns>
        public Menu GetMenusById(int menuId)
        {
            return repo.Single<Menu>(t => t.Id == menuId);
        }

        /// <summary>
        /// To get the Menu 
        /// </summary>
        /// <param name="menuId"></param>
        /// <returns></returns>
        public IList<Menu> GetAllMenus()
        {
            return repo.All<Menu>().ToList<Menu>();
        }

        /// <summary>
        /// Adding the Menu
        /// </summary>
        /// <param name="article"></param>
        /// <returns></returns>
        public TransactionResult AddMenu(Menu menu)
        {
            var t = new TransactionResult();
            var r = repo.Add(menu);
            if (r == null) return new TransactionResult() { Exception = "Null id after save", Successful = false };
            t.Successful = true;
            Cache();
            return t;
        }
        /// <summary>
        /// Updating the existing Menu
        /// </summary>
        /// <param name="article"></param>
        /// <returns></returns>
        public TransactionResult UpdateMenu(Menu menu)
        {
            var t = new TransactionResult();
            t.ReturnVal = repo.Update<Menu>(menu);
            t.Successful = true;
            Cache();
            return t;
        }
        /// <summary>
        /// Delete the Menu
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public TransactionResult DeleteMenu(int id)
        {
            var t = new TransactionResult { Successful = false, ReturnVal = repo.Delete<Menu>(id) };
            if (t.ReturnVal > 0)
                t.Successful = true;
            return t;
        }

        public IList<Listing> GetListingsByUserId(int id)
        {
            return repo.Find<Listing>(t => t.UserId == id);
        }

        public IList<Listing> GetListingsByCounty(int id)
        {
            var list =  (from l in repo.All<Listing>()
                    join c in repo.All<ListingCounty>()
                        on l.Id equals c.Listing
                    where c.County == id
                    select l).ToList();
            foreach (var listing in list)
            {
                listing.Categories = (from cat in Categories
                                      join lcat in repo.All<ListingCategory>() on cat.Id equals lcat.Category
                                      where lcat.Listing == listing.Id
                                      select cat).ToList();
            }
            return list.Where(t => t.Active == true).ToList();
        }

        public County GetCountyById(int id)
        {
            return repo.Single<County>(t => t.Id == id);
        }

        public IList<Article> GetAllArticles()
        {
            return (Articles == null || Articles.Count == 0) ? repo.All<Article>().ToList() : Articles;
        }

        public void DeleteCategoryAndCountyForListing(int id)
        {
            var c = repo.Find<ListingCategory>(t => t.Listing == id);
            repo.DeleteMany(c);
            var x = repo.Find<ListingCounty>(t => t.Listing == id);
            repo.DeleteMany(x);
        }

        public IList<Menu> GetMenuByName(string name)
        {
            return repo.Find<Menu>(t => t.Name == name);
        }


        /*Menu Coding End here*/
    }
}